#!/usr/bin/env node

const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

console.log('🔄 重新创建所有设备实例表...\n');

try {
  const db = new Database(DB_PATH);
  
  console.log('1. 获取所有设备类型...');
  
  // 获取所有唯一的设备类型
  const devices = db.prepare(`
    SELECT DISTINCT 
      d.device_id,
      d.classification_code,
      d.specialty,
      dtm.english_name
    FROM devices d
    LEFT JOIN device_type_mappings dtm ON d.classification_code = dtm.classification_code
    ORDER BY d.specialty, d.classification_code
  `).all();
  
  console.log(`找到 ${devices.length} 个不同的设备类型`);
  
  // 专业前缀映射
  const specialtyPrefixes = {
    '配管专业': 'piping',
    '电气专业': 'electrical',
    '暖通专业': 'hvac',
    '通信专业': 'communication',
    '仪表专业': 'instrumentation'
  };
  
  console.log('\n2. 创建设备实例表...');
  
  let createdCount = 0;
  let skippedCount = 0;
  let errorCount = 0;
  
  devices.forEach((device, index) => {
    const { device_id, classification_code, specialty, english_name } = device;
    
    // 生成表名 - 优先使用模板导入格式，回退到传统格式
    let tableName;
    if (english_name && specialtyPrefixes[specialty]) {
      tableName = `device_instances_${specialtyPrefixes[specialty]}_${english_name}`;
    } else {
      tableName = `device_instances_${device_id.replace(/-/g, '_')}`;
    }
    
    console.log(`[${index + 1}/${devices.length}] 处理: ${device_id} -> ${tableName}`);
    
    try {
      // 检查表是否已存在
      const exists = db.prepare(`
        SELECT name FROM sqlite_master 
        WHERE type='table' AND name = ?
      `).get(tableName);
      
      if (exists) {
        console.log(`  ✓ 表已存在，跳过`);
        skippedCount++;
        return;
      }
      
      // 创建设备实例表
      const createTableSQL = `
        CREATE TABLE [${tableName}] (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          device_type_code TEXT NOT NULL,
          instance_number TEXT NOT NULL,
          object_number TEXT NOT NULL,
          object_description TEXT,
          naming_record_id INTEGER,
          position_record_id INTEGER,
          base_params_record_id INTEGER,
          created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          UNIQUE(device_type_code, instance_number)
        )
      `;
      
      db.prepare(createTableSQL).run();
      console.log(`  ✓ 创建成功`);
      createdCount++;
      
    } catch (error) {
      console.log(`  ✗ 创建失败: ${error.message}`);
      errorCount++;
    }
  });
  
  console.log('\n3. 检查增强表结构...');
  
  // 检查是否有enhance脚本需要的字段映射表
  const fieldMappingExists = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name = 'instance_field_mappings'
  `).get();
  
  if (!fieldMappingExists) {
    console.log('  创建字段映射表...');
    const createFieldMappingSQL = `
      CREATE TABLE IF NOT EXISTS instance_field_mappings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        instance_table_name TEXT NOT NULL,
        field_name TEXT NOT NULL,
        source_table TEXT NOT NULL,
        source_table_type TEXT NOT NULL,
        field_type TEXT NOT NULL,
        is_required INTEGER DEFAULT 0,
        description TEXT,
        created_at TEXT DEFAULT (datetime('now')),
        UNIQUE(instance_table_name, field_name)
      )
    `;
    db.prepare(createFieldMappingSQL).run();
    console.log('  ✓ 字段映射表创建成功');
  } else {
    console.log('  ✓ 字段映射表已存在');
  }
  
  console.log('\n4. 统计结果...');
  
  // 获取最终的表数量
  const finalInstanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();
  
  console.log(`设备实例表总数: ${finalInstanceTables.length}`);
  console.log(`新创建: ${createdCount}`);
  console.log(`跳过已存在: ${skippedCount}`);
  console.log(`创建失败: ${errorCount}`);
  
  // 按专业统计
  const specialtyStats = {};
  finalInstanceTables.forEach(table => {
    const tableName = table.name;
    let specialty = '其他';
    
    for (const [chineseSpecialty, prefix] of Object.entries(specialtyPrefixes)) {
      if (tableName.includes(`_${prefix}_`)) {
        specialty = chineseSpecialty;
        break;
      }
    }
    
    if (!specialtyStats[specialty]) {
      specialtyStats[specialty] = 0;
    }
    specialtyStats[specialty]++;
  });
  
  console.log('\n5. 按专业统计:');
  Object.entries(specialtyStats).forEach(([specialty, count]) => {
    console.log(`  ${specialty}: ${count}个表`);
  });
  
  // 显示一些示例表
  console.log('\n6. 示例表名:');
  finalInstanceTables.slice(0, 10).forEach(table => {
    console.log(`  - ${table.name}`);
  });
  
  if (finalInstanceTables.length > 10) {
    console.log(`  ... 还有 ${finalInstanceTables.length - 10} 个表`);
  }
  
  db.close();
  
  console.log('\n✅ 设备实例表重建完成!');
  console.log(`现在总共有 ${finalInstanceTables.length} 个设备实例表`);
  
} catch (error) {
  console.error('❌ 重建过程中发生错误:', error.message);
  console.error(error.stack);
  process.exit(1);
}